package dao;


import model.Book;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class BookDao {

    public static int updateBook(Connection connection, Book book) throws Exception {
        int ret = 0;
        String sql = "UPDATE books SET position = ?, description = ?,author = ?, name = ? WHERE bookid = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1, book.getPosition());
        preparedStatement.setString(2, book.getDescription());
        preparedStatement.setString(3,book.getAuthor());
        preparedStatement.setString(4, book.getName());
        preparedStatement.setInt(5,book.getBookId());
        preparedStatement.executeUpdate();
        return ret;
    }

    public static int deleteById(Connection connection, int bookid) throws Exception {
        int ret = 0;
        String delete = "DELETE FROM books WHERE bookid = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(delete);
        preparedStatement.setInt(1, bookid);
        preparedStatement.executeUpdate();
        return ret;
    }

    public static int insertBook(Connection connection, Book book) throws Exception{
        int ret = 0;
        String getid = "SELECT max(bookid) as 'Max' FROM books";
        PreparedStatement preparedStatement = connection.prepareStatement(getid);
        ResultSet resultSet = preparedStatement.executeQuery();
        resultSet.next();
        int nexId = resultSet.getInt("MAX") + 1;
        String insertSQL = "INSERT INTO books VALUES (?,?,?,?,?,?,?)";
        preparedStatement = connection.prepareStatement(insertSQL);
        preparedStatement.setInt(1,nexId);
        preparedStatement.setString(2, book.getName());
        preparedStatement.setString(3, book.getAuthor());
        preparedStatement.setString(4, book.getIsbn());
        preparedStatement.setInt(5,book.getStatus());
        preparedStatement.setString(6, book.getPosition());
        preparedStatement.setString(7, book.getDescription());
        preparedStatement.executeUpdate();
        return ret;
    }

    public static ResultSet selectByBookid(Connection connection, int bookid) throws Exception{
        String select = "Select * FROM books where bookid = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(select);
        preparedStatement.setInt(1, bookid);
        return preparedStatement.executeQuery();
    }

    public static ResultSet listByName(Connection connection, String bookName) throws Exception{
        String select = "Select * FROM books WHERE name like '%" + bookName + "%'";
        PreparedStatement preparedStatement = connection.prepareStatement(select);
        return preparedStatement.executeQuery();
    }

    public static ResultSet getAll(Connection connection) throws Exception {
        String select = "Select * FROM books";
        PreparedStatement preparedStatement = connection.prepareStatement(select);
        return preparedStatement.executeQuery();
    }

}
